package com.centit.util.excelUtil;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFCellUtil;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.util.CellRangeAddress;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;

public class ExportExcel {
	//区划吗
	private String jgCode;
	 public String getJgCode() {
		return jgCode;
	}

	public void setJgCode(String jgCode) {
		this.jgCode = jgCode;
	}
	//区划吗
	private String type;
	public String getType() {
		return type;
	}

	public void setType(String type) {
		this.type = type;
	}
	//显示的导出表的标题
    private String title;
    //导出表的列名表头
    private JSONArray jsonArray ;
    private String[] keys;
    private HSSFCellStyle columnTopStyle;

    public JSONArray getJsonArray() {
        return jsonArray;
    }

    public void setJsonArray(JSONArray jsonArray) {
        this.jsonArray = jsonArray;
    }

    public HSSFCellStyle getColumnTopStyle() {
        return columnTopStyle;
    }

    public void setColumnTopStyle(HSSFCellStyle columnTopStyle) {
        this.columnTopStyle = columnTopStyle;
    }

    private JSONArray  dataList;
    
    HttpServletResponse  response;
    
    public HttpServletResponse getResponse() {
		return response;
	}

	public void setResponse(HttpServletResponse response) {
		this.response = response;
	}
	public ExportExcel() {
		
	};
	//构造方法，传入要导出的数据
    public ExportExcel( String type, String title, JSONArray jsonArray, JSONArray  dataList,String[] keys, HttpServletResponse  response ){
        this.dataList = dataList;
        this.jsonArray = jsonArray;
        this.title = title;
        this.type = type;
        this.keys=keys;
        this.response = response;
    }
            
    /*
     * 导出数据
     * */
    public void export() throws Exception{
        OutputStream out = null;
        try{
            HSSFWorkbook workbook = new HSSFWorkbook();                        // 创建工作簿对象
            HSSFSheet sheet = workbook.createSheet(title);                     // 创建工作表

            // 产生表格标题行
            HSSFRow rowm = sheet.createRow(0);
            HSSFCell cellTiltle = rowm.createCell(0);

            //sheet样式定义【getColumnTopStyle()/getStyle()均为自定义方法 - 在下面  - 可扩展】
            columnTopStyle = this.getColumnTopStyle(workbook);//获取列头样式对象
            HSSFCellStyle style = this.getStyle(workbook);                    //单元格样式对象
            HSSFCellStyle zdStyle = this.getZdStyle(workbook);                    //单元格样式对象

            // 定义所需列数
            int columnNum = getColumnCount(jsonArray,0);
            sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, columnNum-1));
            cellTiltle.setCellStyle(columnTopStyle);
            cellTiltle.setCellValue(title);


            // 将列头设置到sheet的单元格中
           sheet= setHeader(jsonArray,sheet,columnNum,columnTopStyle);
            //将查询出的数据设置到sheet对应的单元格中
            if (dataList!=null&&dataList.size()>0){
                for(int i=0;i<dataList.size();i++){

                    JSONObject obj = dataList.getJSONObject(i);//遍历每个对象
                    HSSFRow row = sheet.createRow(sheet.getLastRowNum()+1);//创建所需的行数

                    for(int j=0; j<keys.length; j++){
                        HSSFCell  cell = null;   //设置单元格的数据类型
                        if(j == 0){
                            cell = row.createCell(j,HSSFCell.CELL_TYPE_NUMERIC);
                            cell.setCellValue(i+1);
                            cell.setCellStyle(style);
                        }else{
                            cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
                            if(obj.containsKey(keys[j])&& !"".equals(obj.getString(keys[j])) && obj.getString(keys[j]) != null){
                                cell.setCellValue(obj.getString(keys[j]));//设置单元格的值
                                cell.setCellStyle(style);
                            }else{
                                cell.setCellValue("");
                                cell.setCellStyle(style);
                            }
                            if(obj.containsKey("cells")&&"projectName".equals(keys[j])){
                                //解决合并之后样式丢失问题
                                setRegionStyle(sheet,
                                        new CellRangeAddress(row.getRowNum(), row.getRowNum(), cell.getColumnIndex(), cell.getColumnIndex()+obj.getInteger("cells")),
                                        columnTopStyle);
                                cell.setCellStyle(zdStyle);
                            } if(obj.containsKey("cells")&&("projectTotalInvest".equals(keys[j])||"projectAnualInvest".equals(keys[j])||"lastCompletedInvest".equals(keys[j])||"monthTz".equals(keys[j])||"percentYear".equals(keys[j]))){
                                cell.setCellStyle(zdStyle);
                            }
                        }
                    }
                }
            }
            //让列宽随着导出的列长自动适应
            for (int colNum = 0; colNum < columnNum; colNum++) {
                int columnWidth = sheet.getColumnWidth(colNum) / 256;
                for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
                    HSSFRow currentRow;
                    //当前行未被使用过
                    if (sheet.getRow(rowNum) == null) {
                        currentRow = sheet.createRow(rowNum);
                    } else {
                        currentRow = sheet.getRow(rowNum);
                    }
                    if (currentRow.getCell(colNum) != null) {
                        HSSFCell currentCell = currentRow.getCell(colNum);
                        if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                            int length = currentCell.getStringCellValue().getBytes().length;
                            if (columnWidth < length) {
                                columnWidth = length;
                            }
                        }
                    }
                }
                if(colNum == 0){
                	//序号的行判断有问题 太短了 这里先固定成2000 2020年3月4日16:26:30 曹顺
                	sheet.setColumnWidth(colNum, 2000  );
                    //sheet.setColumnWidth(colNum, (columnWidth-2) );
                }else{
                    sheet.setColumnWidth(colNum, 6000);
                }
//                int colWidth = sheet.getColumnWidth(colNum)*2;
//                if(colWidth<255*256){
//                    sheet.setColumnWidth(colNum, colWidth < 3000 ? 3000 : colWidth);    
//                }else{
//                    sheet.setColumnWidth(colNum,6000 );
//                }
            }

            if(workbook !=null){
                try
                {
                	type = URLEncoder.encode(type, "UTF-8"); 
                	response.reset(); 
                    String fileName = type+"_"+new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(System.currentTimeMillis())+".xls";
                    response = getResponse();
                    response.setContentType("application/vnd.ms-excel;charset=UTF-8"); 
                    response.setHeader("Content-Disposition", "attachment;filename=" + fileName); 
                    out = response.getOutputStream();
                    workbook.write(out);
                }
                catch (IOException e)
                {
                    e.printStackTrace();
                }
            }

        }catch(Exception e){
            e.printStackTrace();
        } finally {
            if(out != null) {
                out.close();
            }
        }
        
    }
//    public void autoSizeColumn(int column, boolean useMergedCells) { 
//        double width = sheet.getColumnWidth(this, column, useMergedCells); 
//
//        if (width != -1) { 
//            width *= 256; 
//            int maxColumnWidth = 255*256; // The maximum column width 
//       for anindividualcell is 255 characters 
//            if (width > maxColumnWidth) {  
//                width = maxColumnWidth; 
//            } 
//            setColumnWidth(column, (int)(width)); 
//        } 
//
//    } 
    /* 
     * 列头单元格样式
     */    
      public HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {
          
            // 设置字体
          HSSFFont font = workbook.createFont();
          //设置字体大小
          font.setFontHeightInPoints((short)11);
          //字体加粗
          font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
          //设置字体名字 
          font.setFontName("Courier New");
          //设置样式; 
          HSSFCellStyle style = workbook.createCellStyle();
          //设置底边框; 
          style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
          //设置底边框颜色;  
          style.setBottomBorderColor(HSSFColor.BLACK.index);
          //设置左边框;   
          style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
          //设置左边框颜色; 
          style.setLeftBorderColor(HSSFColor.BLACK.index);
          //设置右边框; 
          style.setBorderRight(HSSFCellStyle.BORDER_THIN);
          //设置右边框颜色; 
          style.setRightBorderColor(HSSFColor.BLACK.index);
          //设置顶边框; 
          style.setBorderTop(HSSFCellStyle.BORDER_THIN);
          //设置顶边框颜色;  
          style.setTopBorderColor(HSSFColor.BLACK.index);
          //在样式用应用设置的字体;  
          style.setFont(font);
          //设置自动换行; 
          style.setWrapText(false);
          //设置水平对齐的样式为居中对齐;  
          style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
          //设置垂直对齐的样式为居中对齐; 
          style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
          
          return style;
          
      }
    /**
     * 设置合并单元格的边框样式
     *
     * @param sheet 当前表格
     * @param region  要合并的元素
     * @param cs  设置的样式（使用未合并的单元格的样式即可）
     */
    public static void setRegionStyle(HSSFSheet sheet, CellRangeAddress region, HSSFCellStyle cs) {
        for (int i = region.getFirstRow(); i <= region.getLastRow(); i++) {
            HSSFRow row = HSSFCellUtil.getRow(i, sheet);
            for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++) {
                HSSFCell cell = HSSFCellUtil.getCell(row, (short) j);
                cell.setCellStyle(cs);
            }
        }
        sheet.addMergedRegion(region);
    }

    /**
     * 指定行
     * @param workbook
     * @return
     */
    public HSSFCellStyle getZdStyle(HSSFWorkbook workbook){
        // 设置字体
        HSSFFont font = workbook.createFont();
        //设置字体大小
        font.setFontHeightInPoints((short)10);
        //字体加粗
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        //设置字体名字
        font.setFontName("Courier New");
        //设置样式;
        HSSFCellStyle style = workbook.createCellStyle();
        //设置底边框;
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        //设置底边框颜色;
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        //设置左边框;
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        //设置左边框颜色;
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        //设置右边框;
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        //设置右边框颜色;
        style.setRightBorderColor(HSSFColor.BLACK.index);
        //设置顶边框;
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        //设置顶边框颜色;
        style.setTopBorderColor(HSSFColor.BLACK.index);
        //在样式用应用设置的字体;
        style.setFont(font);
        //设置自动换行;
        style.setWrapText(false);
        //设置水平对齐的样式为居中对齐;
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        return style;

    }
      /*
     * 列数据信息单元格样式
     */  
      public HSSFCellStyle getStyle(HSSFWorkbook workbook) {
            // 设置字体
            HSSFFont font = workbook.createFont();
            //设置字体大小
            //font.setFontHeightInPoints((short)10);
            //字体加粗
            //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            //设置字体名字 
            font.setFontName("Courier New");
            //设置样式; 
            HSSFCellStyle style = workbook.createCellStyle();
            //设置底边框; 
            style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            //设置底边框颜色;  
            style.setBottomBorderColor(HSSFColor.BLACK.index);
            //设置左边框;   
            style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            //设置左边框颜色; 
            style.setLeftBorderColor(HSSFColor.BLACK.index);
            //设置右边框; 
            style.setBorderRight(HSSFCellStyle.BORDER_THIN);
            //设置右边框颜色; 
            style.setRightBorderColor(HSSFColor.BLACK.index);
            //设置顶边框; 
            style.setBorderTop(HSSFCellStyle.BORDER_THIN);
            //设置顶边框颜色;  
            style.setTopBorderColor(HSSFColor.BLACK.index);
            //在样式用应用设置的字体;  
            style.setFont(font);
            //设置自动换行; 
            style.setWrapText(false);
            //设置水平对齐的样式为居中对齐;  
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            //设置垂直对齐的样式为居中对齐; 
            style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
           
            return style;
      
      }
      public int getColumnCount(JSONArray jsonArray,int count){
          for (int i=0;i<jsonArray.size();i++){
              if (jsonArray.getJSONObject(i).containsKey("children")){
                  JSONArray two=jsonArray.getJSONObject(i).getJSONArray("children");
                  if(two.size()>0){
                      count= getColumnCount(two,count);
                  }else {
                      count++;
                  }

              }else {
                  count++;
              }
          }
          return count;
      }
      /**
       *功能描述 设置表头
       * @author 皇甫壮
       * @date 2019-7-13
       * @param jsonArray, sheet, columnNum
       * @return org.apache.poi.hssf.usermodel.HSSFSheet
       */
      public HSSFSheet setHeader(JSONArray jsonArray,HSSFSheet sheet,int columnNum,HSSFCellStyle columnTopStyle){
          HSSFRow rowRowName = null;
          HSSFCell  cellRowName=null;
          HSSFCell  cellRowNametwo=null;
          int curr=0;
          int level=0;
          for (int i=0;i<jsonArray.size();i++){ //目前只写了支持1-2级的复杂表头
              level=jsonArray.getJSONObject(i).getInteger("level");//级别
              if(sheet.getRow(level+1)==null){
                  rowRowName = sheet.createRow(level+1);
              }else {
                  rowRowName=sheet.getRow(level+1);
              }
              if (jsonArray.getJSONObject(i).containsKey("children")){
                  JSONArray two=jsonArray.getJSONObject(i).getJSONArray("children");
                  if(two.size()>0){
                      cellRowName = rowRowName.createCell(curr);                //创建列头对应个数的单元格
                      cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);                //设置列头单元格的数据类型
                      HSSFRichTextString text = new HSSFRichTextString(jsonArray.getJSONObject(i).getString("title"));
                      cellRowName.setCellValue(text);                                    //设置列头单元格的值
                      cellRowName.setCellStyle(columnTopStyle);
                      HSSFRow rowRowNametwo = null;
                      for (int j=0;j<two.size();j++){
                          level=two.getJSONObject(j).getInteger("level");//级别
                          if(sheet.getRow(level+1)==null){
                              rowRowNametwo = sheet.createRow(level+1);
                          }else {
                              rowRowNametwo=sheet.getRow(level+1);
                          }
                          cellRowNametwo = rowRowNametwo.createCell(curr);                //创建列头对应个数的单元格
                          cellRowNametwo.setCellType(HSSFCell.CELL_TYPE_STRING);                //设置列头单元格的数据类型
                          HSSFRichTextString texttwo = new HSSFRichTextString(two.getJSONObject(j).getString("title"));
                          cellRowNametwo.setCellValue(texttwo);                                    //设置列头单元格的值
                          cellRowNametwo.setCellStyle(columnTopStyle);
                          curr++;
                      }
                      //解决合并之后样式丢失问题
                      setRegionStyle(sheet,
                              new CellRangeAddress(rowRowName.getRowNum(), rowRowName.getRowNum(), cellRowName.getColumnIndex(), cellRowName.getColumnIndex()+two.size()-1),
                              columnTopStyle);
                      //sheet.addMergedRegion(new CellRangeAddress(rowRowName.getRowNum(), rowRowName.getRowNum(), cellRowName.getColumnIndex(), cellRowName.getColumnIndex()+two.size()-1));
                  }else {
                      cellRowName = rowRowName.createCell(curr);                //创建列头对应个数的单元格
                      cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);                //设置列头单元格的数据类型
                      HSSFRichTextString text = new HSSFRichTextString(jsonArray.getJSONObject(i).getString("title"));
                      cellRowName.setCellValue(text);                                    //设置列头单元格的值
                      setRegionStyle(sheet,
                              new CellRangeAddress(rowRowName.getRowNum(), rowRowName.getRowNum()+1, cellRowName.getColumnIndex(), cellRowName.getColumnIndex()),
                              columnTopStyle);
                     // sheet.addMergedRegion(new CellRangeAddress(rowRowName.getRowNum(), rowRowName.getRowNum()+1, cellRowName.getColumnIndex(), cellRowName.getColumnIndex()));
                      cellRowName.setCellStyle(columnTopStyle);
                      curr++;
                  }

              }else {
                  cellRowName = rowRowName.createCell(curr);                //创建列头对应个数的单元格
                  cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);                //设置列头单元格的数据类型
                  HSSFRichTextString text = new HSSFRichTextString(jsonArray.getJSONObject(i).getString("title"));
                  cellRowName.setCellValue(text);                                    //设置列头单元格的值
                  setRegionStyle(sheet,
                          new CellRangeAddress(rowRowName.getRowNum(), rowRowName.getRowNum()+1, cellRowName.getColumnIndex(), cellRowName.getColumnIndex()),
                          columnTopStyle);

                //  sheet.addMergedRegion(new CellRangeAddress(rowRowName.getRowNum(), rowRowName.getRowNum()+1, cellRowName.getColumnIndex(), cellRowName.getColumnIndex()));
                  cellRowName.setCellStyle(columnTopStyle);
                  curr++;
              }

          }
          return sheet;
      }

    /**
     *功能描述 设置表头(多表)
     * @author zs
     * @date 2019-7-23
     * @param jsonArray, sheet, columnNum
     * @return org.apache.poi.hssf.usermodel.HSSFSheet
     */
    public HSSFSheet setHeader1(JSONArray jsonArray,HSSFSheet sheet,int columnNum,HSSFCellStyle columnTopStyle,int rowNum){
        HSSFRow rowRowName = null;
        HSSFCell  cellRowName=null;
        HSSFCell  cellRowNametwo=null;
        int curr=0;
        int level=0;
        for (int i=0;i<jsonArray.size();i++){ //目前只写了支持1-2级的复杂表头
           level=jsonArray.getJSONObject(i).getInteger("level");//级别
            if(sheet.getRow(level+rowNum)==null){
                rowRowName = sheet.createRow(level+rowNum);
            }else {
                rowRowName=sheet.getRow(level+rowNum);
            }
            if (jsonArray.getJSONObject(i).containsKey("children")){
                JSONArray two=jsonArray.getJSONObject(i).getJSONArray("children");
                if(two.size()>0){
                    cellRowName = rowRowName.createCell(curr);                //创建列头对应个数的单元格
                    cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);                //设置列头单元格的数据类型
                    HSSFRichTextString text = new HSSFRichTextString(jsonArray.getJSONObject(i).getString("title"));
                    cellRowName.setCellValue(text);                                    //设置列头单元格的值
                    cellRowName.setCellStyle(columnTopStyle);
                    HSSFRow rowRowNametwo = null;
                    for (int j=0;j<two.size();j++){
                        level=two.getJSONObject(j).getInteger("level");//级别
                        if(sheet.getRow(level+rowNum)==null){
                            rowRowNametwo = sheet.createRow(level+rowNum);
                        }else {
                            rowRowNametwo=sheet.getRow(level+rowNum);
                        }
                        cellRowNametwo = rowRowNametwo.createCell(curr);                //创建列头对应个数的单元格
                        cellRowNametwo.setCellType(HSSFCell.CELL_TYPE_STRING);                //设置列头单元格的数据类型
                        HSSFRichTextString texttwo = new HSSFRichTextString(two.getJSONObject(j).getString("title"));
                        cellRowNametwo.setCellValue(texttwo);                                    //设置列头单元格的值
                        cellRowNametwo.setCellStyle(columnTopStyle);
                        curr++;
                    }
                    //解决合并之后样式丢失问题
                    setRegionStyle(sheet,
                            new CellRangeAddress(rowRowName.getRowNum(), rowRowName.getRowNum(), cellRowName.getColumnIndex(), cellRowName.getColumnIndex()+two.size()-1),
                            columnTopStyle);
                    //sheet.addMergedRegion(new CellRangeAddress(rowRowName.getRowNum(), rowRowName.getRowNum(), cellRowName.getColumnIndex(), cellRowName.getColumnIndex()+two.size()-1));
                }else {
                    cellRowName = rowRowName.createCell(curr);                //创建列头对应个数的单元格
                    cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);                //设置列头单元格的数据类型
                    HSSFRichTextString text = new HSSFRichTextString(jsonArray.getJSONObject(i).getString("title"));
                    cellRowName.setCellValue(text);                                    //设置列头单元格的值
                    setRegionStyle(sheet,
                            new CellRangeAddress(rowRowName.getRowNum(), rowRowName.getRowNum()+1, cellRowName.getColumnIndex(), cellRowName.getColumnIndex()),
                            columnTopStyle);
                    // sheet.addMergedRegion(new CellRangeAddress(rowRowName.getRowNum(), rowRowName.getRowNum()+1, cellRowName.getColumnIndex(), cellRowName.getColumnIndex()));
                    cellRowName.setCellStyle(columnTopStyle);
                    curr++;
                }

            }else {
                cellRowName = rowRowName.createCell(curr);                //创建列头对应个数的单元格
                cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);                //设置列头单元格的数据类型
                HSSFRichTextString text = new HSSFRichTextString(jsonArray.getJSONObject(i).getString("title"));
                cellRowName.setCellValue(text);                                    //设置列头单元格的值
                setRegionStyle(sheet,
                        new CellRangeAddress(rowRowName.getRowNum(), rowRowName.getRowNum()+1, cellRowName.getColumnIndex(), cellRowName.getColumnIndex()),
                        columnTopStyle);

                //  sheet.addMergedRegion(new CellRangeAddress(rowRowName.getRowNum(), rowRowName.getRowNum()+1, cellRowName.getColumnIndex(), cellRowName.getColumnIndex()));
                cellRowName.setCellStyle(columnTopStyle);
                curr++;
            }

        }
        return sheet;
    }

    /*
     * 导出数据
     * */
    public void export1(JSONArray  dataList1) throws Exception{
        try{
            HSSFWorkbook workbook = new HSSFWorkbook();                        // 创建工作簿对象
            HSSFSheet sheet = workbook.createSheet(title);                     // 创建工作表

            // 产生表格标题行
            HSSFRow rowm = sheet.createRow(0);
            HSSFCell cellTiltle = rowm.createCell(0);

            //sheet样式定义【getColumnTopStyle()/getStyle()均为自定义方法 - 在下面  - 可扩展】
            columnTopStyle = this.getColumnTopStyle(workbook);//获取列头样式对象
            HSSFCellStyle style = this.getStyle(workbook);                    //单元格样式对象
            // 定义所需列数
            int columnNum = getColumnCount(jsonArray,0);
            sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, columnNum-1));
            cellTiltle.setCellStyle(columnTopStyle);
            cellTiltle.setCellValue(title);


            // 将列头设置到sheet的单元格中
            sheet= setHeader(jsonArray,sheet,columnNum,columnTopStyle);
            //将查询出的数据设置到sheet对应的单元格中
            if (dataList!=null&&dataList.size()>0){
                for(int i=0;i<dataList.size();i++){

                    JSONObject obj = dataList.getJSONObject(i);//遍历每个对象
                    HSSFRow row = sheet.createRow(sheet.getLastRowNum()+1);//创建所需的行数

                    for(int j=0; j<keys.length; j++){
                        HSSFCell  cell = null;   //设置单元格的数据类型
                        if(j == 0){
                            cell = row.createCell(j,HSSFCell.CELL_TYPE_NUMERIC);
                            cell.setCellValue(i+1);
                        }else{
                            cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
                            if(obj.containsKey(keys[j])&& !"".equals(obj.getString(keys[j])) && obj.getString(keys[j]) != null){
                                cell.setCellValue(obj.getString(keys[j]));//设置单元格的值
                            }else{
                                cell.setCellValue("");
                            }
                            if(obj.containsKey("cells")&&"projectName".equals(keys[j])){
                                //解决合并之后样式丢失问题
                                setRegionStyle(sheet,
                                        new CellRangeAddress(row.getRowNum(), row.getRowNum(), cell.getColumnIndex(), cell.getColumnIndex()+obj.getInteger("cells")),
                                        columnTopStyle);
                            }
                        }
                        cell.setCellStyle(style);
                    }
                }
            }
            sheet= setHeader1(jsonArray,sheet,columnNum,columnTopStyle,sheet.getLastRowNum()+2);
            if (dataList1!=null&&dataList1.size()>0){
                for(int i=0;i<dataList1.size();i++){

                    JSONObject obj = dataList1.getJSONObject(i);//遍历每个对象
                    HSSFRow row = sheet.createRow(sheet.getLastRowNum()+1);//创建所需的行数

                    for(int j=0; j<keys.length; j++){
                        HSSFCell  cell = null;   //设置单元格的数据类型
                        if(j == 0){
                            cell = row.createCell(j,HSSFCell.CELL_TYPE_NUMERIC);
                            cell.setCellValue(i+1);
                        }else{
                            cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
                            if(obj.containsKey(keys[j])&& !"".equals(obj.getString(keys[j])) && obj.getString(keys[j]) != null){
                                cell.setCellValue(obj.getString(keys[j]));//设置单元格的值
                            }else{
                                cell.setCellValue("");
                            }
                            if(obj.containsKey("cells")&&"projectName".equals(keys[j])){
                                //解决合并之后样式丢失问题
                                setRegionStyle(sheet,
                                        new CellRangeAddress(row.getRowNum(), row.getRowNum(), cell.getColumnIndex(), cell.getColumnIndex()+obj.getInteger("cells")),
                                        columnTopStyle);
                            }
                        }
                        cell.setCellStyle(style);
                    }
                }
            }
            //让列宽随着导出的列长自动适应
            for (int colNum = 0; colNum < columnNum; colNum++) {
                int columnWidth = sheet.getColumnWidth(colNum) / 256;
                for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
                    HSSFRow currentRow;
                    //当前行未被使用过
                    if (sheet.getRow(rowNum) == null) {
                        currentRow = sheet.createRow(rowNum);
                    } else {
                        currentRow = sheet.getRow(rowNum);
                    }
                    if (currentRow.getCell(colNum) != null) {
                        HSSFCell currentCell = currentRow.getCell(colNum);
                        if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                            int length = currentCell.getStringCellValue().getBytes().length;
                            if (columnWidth < length) {
                                columnWidth = length;
                            }
                        }
                    }
                }
                if(colNum == 0){
                    sheet.setColumnWidth(colNum, (columnWidth-2) );
                }else{
                    sheet.setColumnWidth(colNum, 6000);
                }
//                int colWidth = sheet.getColumnWidth(colNum)*2;
//                if(colWidth<255*256){
//                    sheet.setColumnWidth(colNum, colWidth < 3000 ? 3000 : colWidth);
//                }else{
//                    sheet.setColumnWidth(colNum,6000 );
//                }
            }

            if(workbook !=null){
                try
                {
                    type = URLEncoder.encode(type, "UTF-8");
                    response.reset();
                    String fileName = "["+type +"]"+"["+jgCode+"]" +"["+new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(System.currentTimeMillis())+"]"+ ".xls";
                    response = getResponse();
                    response.setContentType("application/vnd.ms-excel;charset=UTF-8");
                    response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
                    OutputStream out = response.getOutputStream();
                    workbook.write(out);
                }
                catch (IOException e)
                {
                    e.printStackTrace();
                }
            }

        }catch(Exception e){
            e.printStackTrace();
        }

    }

    /*
    * 导出数据
    * */
    public void export2() throws Exception{
        try{
            HSSFWorkbook workbook = new HSSFWorkbook();                        // 创建工作簿对象
            int count = dataList.size();
            int num = count%50000;
            int num1;
            if(num==0){
                num1=count/50000;
            }else{
                num1=count/50000+1;
            }
            for(int a=1;a<=num1;a++) {
                HSSFSheet sheet = workbook.createSheet(title+a);                     // 创建工作表

                // 产生表格标题行
                HSSFRow rowm = sheet.createRow(0);
                HSSFCell cellTiltle = rowm.createCell(0);

                //sheet样式定义【getColumnTopStyle()/getStyle()均为自定义方法 - 在下面  - 可扩展】
                columnTopStyle = this.getColumnTopStyle(workbook);//获取列头样式对象
                HSSFCellStyle style = this.getStyle(workbook);                    //单元格样式对象
                // 定义所需列数
                int columnNum = getColumnCount(jsonArray, 0);
                sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, columnNum - 1));
                cellTiltle.setCellStyle(columnTopStyle);
                cellTiltle.setCellValue(title);


                // 将列头设置到sheet的单元格中
                sheet = setHeader(jsonArray, sheet, columnNum, columnTopStyle);
                //将查询出的数据设置到sheet对应的单元格中
                if (dataList != null && dataList.size() > 0) {
                    for (int i = 50000*a-50000; i < 50000*a && i<count; i++) {

                        JSONObject obj = dataList.getJSONObject(i);//遍历每个对象
                        HSSFRow row = sheet.createRow((i-50000*a+50002)+2);//创建所需的行数

                        for (int j = 0; j < keys.length; j++) {
                            HSSFCell cell = null;   //设置单元格的数据类型
                            if (j == 0) {
                                cell = row.createCell(j, HSSFCell.CELL_TYPE_NUMERIC);
                                cell.setCellValue(i + 1);
                            } else {
                                cell = row.createCell(j, HSSFCell.CELL_TYPE_STRING);
                                if (obj.containsKey(keys[j]) && !"".equals(obj.getString(keys[j])) && obj.getString(keys[j]) != null) {
                                    cell.setCellValue(obj.getString(keys[j]));//设置单元格的值
                                } else {
                                    cell.setCellValue("");
                                }
                                if (obj.containsKey("cells") && "projectName".equals(keys[j])) {
                                    //解决合并之后样式丢失问题
                                    setRegionStyle(sheet,
                                            new CellRangeAddress(row.getRowNum(), row.getRowNum(), cell.getColumnIndex(), cell.getColumnIndex() + obj.getInteger("cells")),
                                            columnTopStyle);
                                }
                            }
                            cell.setCellStyle(style);
                        }
                    }
                }
                //让列宽随着导出的列长自动适应
                for (int colNum = 0; colNum < columnNum; colNum++) {
                    int columnWidth = sheet.getColumnWidth(colNum) / 256;
                    for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
                        HSSFRow currentRow;
                        //当前行未被使用过
                        if (sheet.getRow(rowNum) == null) {
                            currentRow = sheet.createRow(rowNum);
                        } else {
                            currentRow = sheet.getRow(rowNum);
                        }
                        if (currentRow.getCell(colNum) != null) {
                            HSSFCell currentCell = currentRow.getCell(colNum);
                            if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                                int length = currentCell.getStringCellValue().getBytes().length;
                                if (columnWidth < length) {
                                    columnWidth = length;
                                }
                            }
                        }
                    }
                    if (colNum == 0) {
                        sheet.setColumnWidth(colNum, (columnWidth - 2));
                    } else {
                        sheet.setColumnWidth(colNum, 6000);
                    }
//                int colWidth = sheet.getColumnWidth(colNum)*2;
//                if(colWidth<255*256){
//                    sheet.setColumnWidth(colNum, colWidth < 3000 ? 3000 : colWidth);
//                }else{
//                    sheet.setColumnWidth(colNum,6000 );
//                }
                }
            }
            if(workbook !=null){
                try
                {
                    type = URLEncoder.encode(type, "UTF-8");
                    response.reset();
                    String fileName = "["+type +"]"+"["+jgCode+"]" +"["+new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(System.currentTimeMillis())+"]"+ ".xls";
                    response = getResponse();
                    response.setContentType("application/vnd.ms-excel;charset=UTF-8");
                    response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
                    OutputStream out = response.getOutputStream();
                    workbook.write(out);
                }
                catch (IOException e)
                {
                    e.printStackTrace();
                }
            }

        }catch(Exception e){
            e.printStackTrace();
        }

    }
    
    
    /**
     * 没有序号列的导出  从新封装
     * @throws Exception
     */
    public void exportNoNumber() throws Exception{
        OutputStream out = null;
        try{
            HSSFWorkbook workbook = new HSSFWorkbook();                        // 创建工作簿对象
            HSSFSheet sheet = workbook.createSheet(title);                     // 创建工作表

            // 产生表格标题行
            HSSFRow rowm = sheet.createRow(0);
            HSSFCell cellTiltle = rowm.createCell(0);

            //sheet样式定义【getColumnTopStyle()/getStyle()均为自定义方法 - 在下面  - 可扩展】
            columnTopStyle = this.getColumnTopStyle(workbook);//获取列头样式对象
            HSSFCellStyle style = this.getStyle(workbook);                    //单元格样式对象
            HSSFCellStyle zdStyle = this.getZdStyle(workbook);                    //单元格样式对象

            // 定义所需列数
            int columnNum = getColumnCount(jsonArray,0);
            sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, columnNum-1));
            cellTiltle.setCellStyle(columnTopStyle);
            cellTiltle.setCellValue(title);


            // 将列头设置到sheet的单元格中
           sheet= setHeader(jsonArray,sheet,columnNum,columnTopStyle);
            //将查询出的数据设置到sheet对应的单元格中
            if (dataList!=null&&dataList.size()>0){
            	int starrowindex = sheet.getLastRowNum()+1;
            	int rowhb = 0;
            	int rownumhb = 0;
                for(int i=0;i<dataList.size();i++){
                    JSONObject obj = dataList.getJSONObject(i);//遍历每个对象
                    HSSFRow row = sheet.getRow(starrowindex+i);
                    if(row==null) {
                    	row = sheet.createRow(starrowindex+i);//创建所需的行数
                    } 
                    for(int j=0; j<keys.length; j++){
                        HSSFCell  cell = null;   //设置单元格的数据类型
                        cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
                        if(keys[j].equals("nf")) {
                        	if(i == rownumhb+rowhb || i==0) {
                        		rowhb=0;
                        		rownumhb = i;
                        		for(int k=0;k<dataList.size();k++) {
                            		JSONObject objk = dataList.getJSONObject(k);
                            		if(objk.getString("nf").equals(obj.getString("nf"))) {
                            			rowhb++;
                            		}
                            	}
                            	cell.setCellValue(obj.getString(keys[j]));//设置单元格的值
                                cell.setCellStyle(style);
                                setRegionStyle(sheet,
                                        new CellRangeAddress(row.getRowNum(), row.getRowNum()+rowhb-1, cell.getColumnIndex(), cell.getColumnIndex()),
                                        columnTopStyle);
                        	} else {
                        		cell.setCellStyle(style);
                        	}
                        } else {
                        	if(obj.containsKey(keys[j])&& !"".equals(obj.getString(keys[j])) && obj.getString(keys[j]) != null){
                                cell.setCellValue(obj.getString(keys[j]));//设置单元格的值
                                cell.setCellStyle(style);
                            }else{
                                cell.setCellValue("");
                                cell.setCellStyle(style);
                            }
                        }
                    }
                }
            }
            for (int colNum = 0; colNum < columnNum; colNum++) {
            	sheet.setColumnWidth(colNum, 6000);
            }

            if(workbook !=null){
                try
                {
                	type = URLEncoder.encode(type, "UTF-8"); 
                	response.reset(); 
                    String fileName = type+"_"+new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(System.currentTimeMillis())+".xls";
                    response = getResponse();
                    response.setContentType("application/vnd.ms-excel;charset=UTF-8"); 
                    response.setHeader("Content-Disposition", "attachment;filename=" + fileName); 
                    out = response.getOutputStream();
                    workbook.write(out);
                }
                catch (IOException e)
                {
                    e.printStackTrace();
                }
            }

        }catch(Exception e){
            e.printStackTrace();
        } finally {
            if(out != null) {
                out.close();
            }
        }
        
    }
    
    /**
     *	 飞机信息情况导出
     * @throws Exception
     */
    public void exportFjxxqk( String type, String title, JSONArray jsonArray, JSONArray  dataList,String[] keys, JSONArray jsonArraytop, JSONArray  dataListtop,String[] keysTop, HttpServletResponse  response ) throws Exception{
        OutputStream out = null;
        try{
            HSSFWorkbook workbook = new HSSFWorkbook();                        // 创建工作簿对象
            HSSFSheet sheet = workbook.createSheet(title);                     // 创建工作表
            // 产生表格标题行
            HSSFRow rowm = sheet.createRow(0);
            HSSFCell cellTiltle = rowm.createCell(0);
            //sheet样式定义【getColumnTopStyle()/getStyle()均为自定义方法 - 在下面  - 可扩展】
            columnTopStyle = this.getColumnTopStyle(workbook);//获取列头样式对象
            HSSFCellStyle style = this.getStyle(workbook);                    //单元格样式对象
            HSSFCellStyle zdStyle = this.getZdStyle(workbook);                //单元格样式对象
            // 定义所需列数
            int columnNum1 = getColumnCount(jsonArray,0);
            int columnNumtop= getColumnCount(jsonArraytop,0);
            int columnNum =  Math.max(columnNum1, columnNumtop);
            sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, columnNum-1));
            cellTiltle.setCellStyle(columnTopStyle);
            cellTiltle.setCellValue(title);


            // 将列头设置到sheet的单元格中
            sheet= setHeaderAppend(jsonArraytop,sheet,columnNumtop,columnTopStyle,0);
            //将查询出的数据设置到sheet对应的单元格中
            if (dataListtop!=null&&dataListtop.size()>0){
            	int starrowindex = sheet.getLastRowNum()+1;
                for(int i=0;i<dataListtop.size();i++){
                    JSONObject obj = dataListtop.getJSONObject(i);//遍历每个对象
                    HSSFRow row;
                    if(i==0) {
                    	row = sheet.createRow(starrowindex);//创建所需的行数
                    } else {
                    	row = sheet.getRow(starrowindex+i);
                    }
                    for(int j=0; j<keysTop.length; j++){
                        HSSFCell  cell = null;   //设置单元格的数据类型
                        cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
                        if((keysTop[j].equals("hj") || keysTop[j].equals("pjjl") || keysTop[j].equals("gxrq")) && i==0) {
                        	cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
                            if(obj.containsKey(keysTop[j])&& !"".equals(obj.getString(keysTop[j])) && obj.getString(keysTop[j]) != null){
                                cell.setCellValue(obj.getString(keysTop[j]));//设置单元格的值
                            }else{
                                cell.setCellValue("");
                            }
                            setRegionStyle(sheet,
                                    new CellRangeAddress(row.getRowNum(), row.getRowNum()+dataListtop.size()-1, cell.getColumnIndex(), cell.getColumnIndex()),
                                    columnTopStyle);
                        } else {
                        	if(!keysTop[j].equals("hj") && !keysTop[j].equals("pjjl") && !keysTop[j].equals("gxrq")) {
                        		cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
                                if(obj.containsKey(keysTop[j])&& !"".equals(obj.getString(keysTop[j])) && obj.getString(keysTop[j]) != null){
                                    cell.setCellValue(obj.getString(keysTop[j]));//设置单元格的值
                                    cell.setCellStyle(style);
                                }else{
                                    cell.setCellValue("");
                                    cell.setCellStyle(style);
                                }
                        	} else {
                        		cell.setCellStyle(style);
                        	}
                        }
                    }
                }
            }
            
            sheet = setHeaderAppend(jsonArray,sheet,columnNum1,columnTopStyle,sheet.getLastRowNum());
            if (dataList!=null&&dataList.size()>0){
                for(int i=0;i<dataList.size();i++){
                    JSONObject obj = dataList.getJSONObject(i);//遍历每个对象
                    HSSFRow row = sheet.createRow(sheet.getLastRowNum()+1);//创建所需的行数
                    for(int j=0; j<keys.length; j++){
                        HSSFCell  cell = null;   //设置单元格的数据类型
                        if (j == 0) {
                            cell = row.createCell(j, HSSFCell.CELL_TYPE_NUMERIC);
                            cell.setCellValue(i + 1);
                            cell.setCellStyle(style);
                        } else {
                        	cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
                        	if(obj.containsKey(keys[j])&& !"".equals(obj.getString(keys[j])) && obj.getString(keys[j]) != null){
                                cell.setCellValue(obj.getString(keys[j]));//设置单元格的值
                                cell.setCellStyle(style);
                            }else{
                                cell.setCellValue("");
                                cell.setCellStyle(style);
                            }
                        }
                    }
                }
            }
            for (int colNum = 0; colNum < columnNum; colNum++) {
            	sheet.setColumnWidth(colNum, 6000);
            }
            if(workbook !=null){
                try
                {
                	type = URLEncoder.encode(type, "UTF-8"); 
                	response.reset(); 
                    String fileName = type+"_"+new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(System.currentTimeMillis())+".xls";
                    response.setContentType("application/vnd.ms-excel;charset=UTF-8"); 
                    response.setHeader("Content-Disposition", "attachment;filename=" + fileName); 
                    out = response.getOutputStream();
                    workbook.write(out);
                }
                catch (IOException e)
                {
                    e.printStackTrace();
                }
            }

        }catch(Exception e){
            e.printStackTrace();
        } finally {
            if(out != null) {
                out.close();
            }
        }
        
    }
    
    /**
     *	 追加表头
     * @param jsonArray
     * @param sheet
     * @param columnNum
     * @param columnTopStyle
     * @return
     */
    public HSSFSheet setHeaderAppend(JSONArray jsonArray,HSSFSheet sheet,int columnNum,HSSFCellStyle columnTopStyle,int rownum){
        HSSFRow rowRowName = null;
        HSSFCell  cellRowName=null;
        HSSFCell  cellRowNametwo=null;
        int curr=0;
        int level=0;
        for (int i=0;i<jsonArray.size();i++){ //目前只写了支持1-2级的复杂表头
            level=jsonArray.getJSONObject(i).getInteger("level");//级别
            if(sheet.getRow(rownum+level+1)==null){
                rowRowName = sheet.createRow(rownum+level+1);
            }else {
                rowRowName=sheet.getRow(rownum+level+1);
            }
	        cellRowName = rowRowName.createCell(curr);                //创建列头对应个数的单元格
	        cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);                //设置列头单元格的数据类型
	        HSSFRichTextString text = new HSSFRichTextString(jsonArray.getJSONObject(i).getString("title"));
	        cellRowName.setCellValue(text);                                    //设置列头单元格的值
	        cellRowName.setCellStyle(columnTopStyle);
	        curr++;
        }
        return sheet;
    }
}
